from flask import current_app as app
from flask import Blueprint, request, current_app, make_response,jsonify
from xlrd import open_workbook
from utiles.getdata import getdata
from utiles.db import db1
import os
from utiles.myfile import myf
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
bp_company = Blueprint("company",__name__)
import time

def timer(func):
    def wrapper(*args,**kargs):
        stime = time.time()
        func(*args,**kargs)
        endtime = time.time()
        t = endtime-stime
        print('执行了'+str(t)+'秒')
        return func(*args,**kargs)
    return wrapper




#上传excel,并导入数据库
@bp_company.route('/upload',methods=['POST','GET'])
@timer
def upload():
    # time.sleep(1)
    # return 'ok'
    #获取文件
    file = request.files['file']
    newname = myf.rename(file.filename)
    #上传文件
    file.save(os.path.join('./static/files/'+newname))
    #打开文件读取
    # newname = '1559d8529d374dc0b7c857fc7f48f6f9.xls'
    name = 'static/files/'+newname

    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(1, nrows):
        row_date = sh.row_values(i)
        print(row_date[0])
        print(row_date[1])
    return 'ok'
    #封装数据写入数据库


@bp_company.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    sql = "select * from dept limit 0,10"
    res = db1.findall(sql)
    table.write(0, 0, "编号")
    table.write(0, 1, "名称")
    num = 1
    for i in res:
        table.write(num,0,i['id'])
        table.write(num,1,i['name'])
        num+=1

    workbook.close()
    filename = quote("资产列表_{}.xls".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xls' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file

def xtree(data):
    list=[]
    if len(data) <=0:
        return data
    for i in data:
        i['label'] = i['name']
    #先把id封装成字典
    dict = {}
    for i in data:
        dict[i['id']]=i
    #二次遍历
    for j in data:
        pid = j['pid']
        if pid == 0:
            list.append(j)
        else:

            if 'children' not in dict[pid]:
                dict[pid]['children']=[]
            dict[pid]['children'].append(j)
    return list


@bp_company.route('/deptlist')
def deplist():
    sql = "select * from dept"
    res = db1.findall(sql)
    list = xtree(res)
    return jsonify({'code':200,'list':list})

@bp_company.route('/add',methods=["POST"])
def add():
    data = getdata()
    sql = "insert into dept values ('0','%s','%s',%d)"%(data['name'],data['code'],int(data['pid']))
    db1.update(sql)
    return jsonify({"code":200})






